<?php
  require_once("../includes/config.php"); 
  require_once("Classes/PHPExcel.php");
  require_once("Classes/PHPExcel/IOFactory.php");
  $rendererName = PHPExcel_Settings::PDF_RENDERER_MPDF;
  $rendererLibrary = 'MPDF57';
  $rendererLibraryPath = dirname(__FILE__) . '/'.$rendererLibrary;
  
  //Create Excel Object
  $objPHPExcel = new PHPExcel();
  if($_POST['filteration'] == 1)
  {
    //MySql Query, Filters Applied
    $sql = "SELECT * FROM payment_vouchers WHERE owner_id= ?";
    
    $binds['owner_id'] = $_SESSION['user_id'];
  
    if(!empty($_POST['from']) && !empty($_POST['to']))
    {
      $binds['from'] = $_POST['from'];
      $binds['to'] = $_POST['to'];
      $sql .= " and miladi_date between ? AND ?";
    }
                  
    if(!empty($_POST['contract_id']) )
    {
      $binds['contract_id'] = $_POST['contract_id'];
      $sql .= " and contract_id = ?";
    }
          
    if(!empty($_POST['payment_id']))
    {
      $binds['payment_id'] = $_POST['payment_id'];
      $sql .= " and payment_id = ?";
    }
    
    if(!empty($_POST['payment_method']))
    {
      $binds['payment_method'] = $_POST['payment_method'];
      $sql .= " and paymen_method = ?";
    }
    
    $payments = query($sql,$binds);
    //Create Excel Head row
    $objPHPExcel->getProperties()->setCreator("Diafa Application")
                 ->setLastModifiedBy("Diafa Application")
                 ->setTitle("تقرير سندات الصرف")
                 ->setSubject("تقرير سندات الصرف")
                 ->setDescription("تقرير سندات الصرف")
                 ->setKeywords("تقرير سندات الصرف")
                 ->setCategory("تقرير سندات الصرف");
    
    $objPHPExcel->setActiveSheetIndex(0)
              ->setRightToLeft(true)
              ->setCellValue('A1', 'رقم السند')
              ->setCellValue('B1', 'إلى السيد')
              ->setCellValue('C1', 'التاريخ الميلادى')
              ->setCellValue('D1', 'التاريخ الهجرى')
              ->setCellValue('E1', 'المبلغ')
              ->setCellValue('F1', 'من أجل')
              ->setCellValue('G1', 'رقم العقد')
              ->setCellValue('H1', 'أمين الصندوق')
              ->setCellValue('I1', 'طريقة الدفع');

    // Miscellaneous glyphs, UTF-8
    $i = 2;
    foreach($payments as $payment)
    {
      $objPHPExcel->setActiveSheetIndex(0)
                ->setCellValue('A'.$i, $payment['payment_id'])
                ->setCellValue('B'.$i, $payment['recipient'])
                ->setCellValue('C'.$i, $payment['miladi_date'])
                ->setCellValue('D'.$i, $payment['Hijri_date'])
                ->setCellValue('E'.$i, $payment['amount'])
                ->setCellValue('F'.$i, $payment['reason'])
                ->setCellValue('G'.$i, $payment['contract_id'])
                ->setCellValue('H'.$i, $payment['amen_sondoq'])
                ->setCellValue('I'.$i, $payment_method[ $payment['paymen_method'] ]);
      $i++;
    }
    


    $filename = "Payment_report";
    
  }
  else if($_POST['filteration'] == 2) //reciept_reports
  {
    $sql = "SELECT * FROM recipt_vouchers WHERE owner_id= ?";
    $binds['owner_id'] = $_SESSION['user_id'];
  
    if(!empty($_POST['from']) && !empty($_POST['to']))
    {
      $binds['from'] = $_POST['from'];
      $binds['to'] = $_POST['to'];
      $sql .= " and miladi_date between ? AND ?";
    }
                  
    if(!empty($_POST['contract_id']) )
    {
      $binds['contract_id'] = $_POST['contract_id'];
      $sql .= " and contract_id = ?";
    }
          
    if(!empty($_POST['recipet_id']))
    {
      $binds['recipet_id'] = $_POST['recipet_id'];
      $sql .= " and recipet_id = ?";
    }
    
    if(!empty($_POST['payment_method']))
    {
      $binds['payment_method'] = $_POST['payment_method'];
      $sql .= " and paymen_method = ?";
    }
          
    $recipts = query($sql,$binds); 

    //Create Excel Head row
    $objPHPExcel->getProperties()->setCreator("Diafa Application")
                 ->setLastModifiedBy("Diafa Application")
                 ->setTitle("تقرير سندات القبض")
                 ->setSubject("تقرير سندات القبض")
                 ->setDescription("تقرير سندات القبض")
                 ->setKeywords("تقرير سندات القبض")
                 ->setCategory("تقرير سندات القبض");
    
    $objPHPExcel->setActiveSheetIndex(0)
              ->setRightToLeft(true)
              ->setCellValue('A1', 'رقم السند')
              ->setCellValue('B1', 'من السيد')
              ->setCellValue('C1', 'التاريخ الميلادى')
              ->setCellValue('D1', 'التاريخ الهجرى')
              ->setCellValue('E1', 'المبلغ')
              ->setCellValue('F1', 'طريقة الدفع')
              ->setCellValue('G1', 'من أجل')
              ->setCellValue('H1', 'رقم العقد')
              ->setCellValue('I1', 'المستلم');

    // Miscellaneous glyphs, UTF-8
    $i = 2;
    foreach($recipts as $reciept)
    {
      $objPHPExcel->setActiveSheetIndex(0)
                ->setCellValue('A'.$i, $reciept['recipet_id'])
                ->setCellValue('B'.$i, $reciept['payee'])
                ->setCellValue('C'.$i, $reciept['miladi_date'])
                ->setCellValue('D'.$i, $reciept['Hijri_date'])
                ->setCellValue('E'.$i, $reciept['amount'])
                ->setCellValue('F'.$i, $payment_method[ $reciept['paymen_method'] ])
                ->setCellValue('G'.$i, $reciept['reason'])
                ->setCellValue('H'.$i, $reciept['contract_id'])
                ->setCellValue('I'.$i, $reciept['recipient']);
      $i++;
    }
    


    $filename = "Reciept_report";    
  }
  
  else if($_POST['filteration'] == 3) //Bookings Reports
  {
  $sql = 'SELECT * FROM contracts 
  LEFT JOIN clients ON 
    contracts.client_id=clients.id 
  LEFT JOIN companies ON 
    contracts.company_id=companies.company_id 
  
  WHERE contracts.owner_id = ?';
  
  $binds = array();
  $binds['owner_id'] = $_SESSION['user_id'];
	if(!empty($_POST['from']) && !empty($_POST['to']))
  {
    $binds['from'] = $_POST['from'];
    $binds['to'] = $_POST['to'];
    $sql .= " and mildai_from between ? AND ?";
  }
  
  $contracts = query($sql,$binds);

    //Create Excel Head row
    $objPHPExcel->getProperties()->setCreator("Diafa Application")
                 ->setLastModifiedBy("Diafa Application")
                 ->setTitle("تقرير حجوزات العملاء")
                 ->setSubject("تقرير حجوزات العملاء")
                 ->setDescription("تقرير حجوزات العملاء")
                 ->setKeywords("تقرير حجوزات العملاء")
                 ->setCategory("تقرير حجوزات العملاء");
    
    $objPHPExcel->setActiveSheetIndex(0)
              ->setRightToLeft(true)
              ->setCellValue('A1', 'اسم المستأجر')
              ->setCellValue('B1', 'رقم البطاقه')
              ->setCellValue('C1', 'رقم العقد')
              ->setCellValue('D1', 'تاريخ الدخول')
              ->setCellValue('E1', 'تاريخ الخروج')
              ->setCellValue('F1', 'عدد الايام')
              ->setCellValue('G1', 'الايجار اليومى')
              ->setCellValue('H1', 'الاجمالى بعد الخصم')
              ->setCellValue('I1', 'مصاريف الخدمات')
              ->setCellValue('J1', 'المطلوب')
              ->setCellValue('K1', 'المدفوع')
              ->setCellValue('L1', 'المتبقى')
              ->setCellValue('M1', 'الملاحظات');

    // Miscellaneous glyphs, UTF-8
    $i = 2;
    foreach($contracts as $contract)
    {
      $price_discounted = $contract['total'] - (  ( ($contract['discount'] + $contract['co_discount']) )  ) - $contract['fixed_discount'];
      $objPHPExcel->setActiveSheetIndex(0)
                ->setCellValue('A'.$i, $contract['name'])
                ->setCellValue('B'.$i, $contract['identification_number'])
                ->setCellValue('C'.$i, $contract['contract_id'])
                ->setCellValue('D'.$i, $contract['mildai_from'])
                ->setCellValue('E'.$i, $contract['miladi_out'])
                ->setCellValue('F'.$i, $contract['period'])
                ->setCellValue('G'.$i, $contract['chosen_daily'])
                ->setCellValue('H'.$i,$price_discounted )
                ->setCellValue('I'.$i, $contract['services_revenue'])
                ->setCellValue('J'.$i, $price_discounted + $contract['services_revenue'])
                ->setCellValue('K'.$i, $contract['paid'])
                ->setCellValue('L'.$i, $price_discounted + $contract['services_revenue']-$contract['paid'])
                ->setCellValue('M'.$i, $contract['Notes']);
      $i++;
    }
    $filename = "contracts";    
  }
  else if($_POST['filteration'] == 4) //Clients Reports
  {
  $sql = 'SELECT * FROM contracts 
  LEFT JOIN clients ON 
    contracts.client_id=clients.id 
  LEFT JOIN companies ON 
    contracts.company_id=companies.company_id 
  
  WHERE contracts.owner_id = ?';
  
  $binds = array();
    $binds['owner_id'] = $_SESSION['user_id'];
	if(!empty($_POST['from']) && !empty($_POST['to']))
  {
    $binds['from'] = $_POST['from'];
    $binds['to'] = $_POST['to'];
    $sql .= " and mildai_from between ? AND ?";
  }
  if(!empty($_POST['id']))
  {
    $binds['id'] = $_POST['id'];
    $sql .= " and identification_number=?";
  }
  if(!empty($_POST['name']))
  {
    $binds['name'] = $_POST['name'];
    $sql .= " and MATCH (name) AGAINST (? IN BOOLEAN MODE) ";
  }
  if(!empty($_POST['mobile']))
  {
    $binds['mobile'] = $_POST['mobile'];
    $sql .= " and mobile=?";
    $sqlCount .= " and mobile=?";
  }
  
  //dump($sql);
  $contracts = query($sql,$binds);

    //Create Excel Head row
    $objPHPExcel->getProperties()->setCreator("Diafa Application")
                 ->setLastModifiedBy("Diafa Application")
                 ->setTitle("تقرير حركة العملاء")
                 ->setSubject("تقرير حركة العملاء")
                 ->setDescription("تقرير حركة العملاء")
                 ->setKeywords("تقرير حركة العملاء")
                 ->setCategory("تقرير حركة العملاء");
    
    $objPHPExcel->setActiveSheetIndex(0)
              ->setRightToLeft(true)
              ->setCellValue('A1', 'أسم العميل')
              ->setCellValue('B1', 'الوحدة')
              ->setCellValue('C1', 'رقم العقد')
              ->setCellValue('D1', 'تاريخ الدخول')
              ->setCellValue('E1', 'تاريخ الخروج')
              ->setCellValue('F1', 'المدة')
              ->setCellValue('G1', 'الإجمالى')
              ->setCellValue('H1', 'المدفوع')
              ->setCellValue('I1', 'المتبقى');

    $i = 2;
    foreach($contracts as $contract)
    {
      $price_discounted = $contract['total'] - (  ( ($contract['discount'] + $contract['co_discount']) )  ) - $contract['fixed_discount'];
      $objPHPExcel->setActiveSheetIndex(0)
                ->setCellValue('A'.$i, $contract['name'])
                ->setCellValue('B'.$i, $contract['apartment_name'])
                ->setCellValue('C'.$i, $contract['contract_id'])
                ->setCellValue('D'.$i, $contract['mildai_from'])
                ->setCellValue('E'.$i, $contract['miladi_out'])
                ->setCellValue('F'.$i, $contract['period'])
                ->setCellValue('G'.$i, $price_discounted + $contract['services_revenue'])
                ->setCellValue('H'.$i, $contract['paid'] )
                ->setCellValue('I'.$i, $price_discounted + $contract['services_revenue']-$contract['paid']);
      $i++;
    }
    $filename = "clients_report";    
  }
  if($_POST['filteration'] == 5){
    $sql_p = "SELECT * FROM payment_vouchers WHERE payment_type = 0 and owner_id= ?";
    $sql_r = "SELECT * FROM recipt_vouchers WHERE payment_type = 0 and owner_id= ?";
    
    $binds['owner_id'] = $_SESSION['user_id'];
  
    if(!empty($_POST['from']) && !empty($_POST['to']))
    {
      $binds['from'] = $_POST['from'];
      $binds['to'] = $_POST['to'];
      $sql_p .= " and miladi_date between ? AND ?";
      $sql_r .= " and miladi_date between ? AND ?";
    }
    $payments_counter = query($sql_p,$binds);
    $reciepts_counter = query($sql_r,$binds);
      
    
    $payments = query($sql_p,$binds);
	$reciepts = query($sql_r,$binds);
  
	$i = 0;
	$box = array();
	$boxes = array();

	while(1)
	{
    if(isset($payments[$i]) || isset($reciepts[$i]))
		{
			if(isset($payments[$i]))
				$payment = $payments[$i];
			else
      {
				$payment = array();
        $reciept['recipet_id'] = "";
				$reciept['amount']= "";
				$reciept['reason']= "";
				$reciept['miladi_date']= "";
      }
			
			if(isset($reciepts[$i]))
				$reciept = $reciepts[$i];
				
			else
			{
				$reciept = array();
				$reciept['recipet_id'] = "";
				$reciept['amount']= "";
				$reciept['reason']= "";
				$reciept['miladi_date']= "";
			}
			
			$box = $payment;
			$box['recipet_id'] = $reciept['recipet_id'];
			$box['reciept_amount'] = $reciept['amount'];
			$box['reciept_reason'] = $reciept['reason'];
			$box['reciept_miladi_date'] = $reciept['miladi_date'];
      $boxes[] = $box;
      $i++;
    }
		else
			break;
	}
      $objPHPExcel->getProperties()->setCreator("Diafa Application")
                 ->setLastModifiedBy("Diafa Application")
                 ->setTitle("تقرير حركة الصندوق")
                 ->setSubject("تقرير حركة الصندوق")
                 ->setDescription("تقرير حركة الصندوق")
                 ->setKeywords("تقرير حركة الصندوق")
                 ->setCategory("تقرير حركة الصندوق");
    
    $objPHPExcel->setActiveSheetIndex(0)
              ->setRightToLeft(true)
              ->setCellValue('A1', 'رقم سند الصرف')
              ->setCellValue('B1', 'بتاريخ')
              ->setCellValue('C1', 'من اجل')
              ->setCellValue('D1', 'مبلغ سند الصرف')
              ->setCellValue('E1', 'رقم سند القبض')
              ->setCellValue('F1', 'بتاريخ')
              ->setCellValue('G1', 'من اجل')
              ->setCellValue('H1', 'مبلغ سند القبض');
			  $filename = "box_reports";
			  $i=2;
              foreach($boxes as $box) {
			   $objPHPExcel->setActiveSheetIndex(0)
			  
			    ->setCellValue('A'.$i, $box['payment_id'])
                ->setCellValue('B'.$i, $box['miladi_date'])
                ->setCellValue('C'.$i, $box['reason'])
                ->setCellValue('D'.$i, $box['amount'])
                ->setCellValue('E'.$i, $box['recipet_id'])
                ->setCellValue('F'.$i, $box['reciept_miladi_date'])
                ->setCellValue('G'.$i, $box['reciept_reason'])
                ->setCellValue('H'.$i, $box['reciept_amount'] ) ;
                
      $i++;
			  
			  }
  
  }
  else if($_POST['filteration'] == 8) //unit Reports
  {
    
	  if(isset($_POST['year']))
  {
    $month = $_POST['month'];
    $year = $_POST['year'];
  }
  else
  {
    $month = intval(date('m', time()) );
    $year = intval(date('Y', time()));
  }
  
  $sql="SELECT * FROM apartments
		WHERE apartments.owner_id=?";
		
  $sqlCount= "SELECT COUNT(*) FROM apartments
		WHERE apartments.owner_id=?";
   
   $binds = array();
   $binds['owner_id'] = $_SESSION['user_id'];
     
  if(!empty($_POST['unit_number']))
  {
    $binds['unit_number'] = $_POST['unit_number'] ;
    $sql .= " and apart_name=?";
    $sqlCount .= " and apart_name=?";
  }
  
  if(!empty($_POST['floor#']))
  {
    if(!empty($binds))
      $and = " and ";
    else
      $and = "  ";
          
    $binds['floor#'] = $_POST['floor#'];
    $sql .= $and." apartments.floor = ?";
    $sqlCount .= $and." apartments.floor = ?";
  }
  
  if(!empty($_POST['room_name']))
  {
    if(!empty($binds))
      $and = " and ";
    else
      $and = "  ";
          
    $binds['room_name'] = $_POST['room_name'];
    $sql .= $and." apart_name = ?";
    $sqlCount .= $and." apart_name = ?";
  }

  $sql .= " ORDER BY apart_name";
  $aparts = query($sql,$binds);
  $marks = array();
  $j = 0;
  
  foreach($aparts as $apart)
  {
    $binds['month'] = $month;
    $binds['year'] = $year;
    $binds['month1'] = $month;
    $binds['year1'] = $year;

    
    $contracts = query("SELECT * FROM contract_apartments LEFT JOIN contracts ON contracts.contract_id = contract_apartments.contract_id LEFT JOIN companies ON contracts.company_id=companies.company_id WHERE apartment_id = ? 
    and (MONTH(actual_in) =? and year(actual_in) =? or (MONTH(created_at) = ? and year(created_at) =?)) ORDER BY actual_in", $apart['id'],$month,$year,$month,$year);
    
    $marks[$j] = array_fill(0, 32, 0);
    $i = 0;
    foreach($contracts as $contract)
    {
	  $i++;
      $timestamp = strtotime($contract['actual_in']);
      $day_in = intval(date('d', $timestamp));

	  //preparing for a scenario where creation date is in current queried month while actual in date in other month
	  $in = date("m",strtotime($contract['actual_in']));
	  if($in > $month)
	  {
		$max_day = cal_days_in_month ( CAL_GREGORIAN, $month, $year ); //return max number of date for a given month, Because I only care about current month
        $end_of_month =  $max_day;
		$created_at = date("d",strtotime($contract['created_at']));
		for ($i = $created_at; $i<=$end_of_month;$i++)
		{
			$marks[$j][$i] = 1;
		}

		continue;
	  }			  		

	  if($contract['actual_ot'] == 0)
		$timestamp1 = strtotime($contract['miladi_out']);
	  else	
		$timestamp1 = strtotime($contract['actual_ot']);
		
      $day_out = intval(date('d', $timestamp1));
      if( $day_out < $day_in ) //$day_out extends to other month
      {
        $max_day = cal_days_in_month ( CAL_GREGORIAN, $month, $year ); //return max number of date for a given month, Because I only care about current month
        $day_out =  $max_day;
      }
      
      for( $k = $day_in; $k <=$day_out;$k++ )
      {
        $marks[$j][$k] = 2;
      }   

      if($contract['created_at'] == 0)
        $created_at = -1;
        
      else
      {
        $timestamp = strtotime($contract['created_at']);
        $created_at = intval(date('d', $timestamp));
      }
	  
	  
      if( $created_at <  $day_in && $created_at != -1 )
      {
        for($k = $created_at; $k <$day_in;$k++)
        {

          if($marks[$j][$k] != 2)
          {
            $marks[$j][$k] = 1;
          }
        }
        

      }
	  
	

      $created_at = -1;
    } 
    
    $j++;

  }
	// table to hold excel cells vs numbers
	$numbers= array();
	$number['B'] = 1;
	$number['C'] = 2;
	$number['D'] = 3;
	$number['E'] = 4;
	$number['F'] = 5;
	$number['G'] = 6;
	$number['H'] = 7;
	$number['I'] = 8;
	$number['J'] = 9;
	$number['K'] = 10;
	$number['L'] = 11;
	$number['M'] = 12;
	$number['N'] = 13;
	$number['O'] = 14;
	$number['P'] = 15;
	$number['Q'] = 16;
	$number['R'] = 17;
	$number['S'] = 18;
	$number['T'] = 19;
	$number['U'] = 20;
	$number['V'] = 21;
	$number['W'] = 22;
	$number['X'] = 23;
	$number['Y'] = 24;
	$number['Z'] = 25;
	$number['AA'] = 26;
	$number['AB'] = 27;
	$number['AC'] = 28;
	$number['AD'] = 29;
	$number['AE'] = 30;
	$number['AF'] = 31;
    $objPHPExcel->getProperties()->setCreator("Diafa Application")
                 ->setLastModifiedBy("Diafa Application")
                 ->setTitle("تقرير الطلبات")
                 ->setSubject("تقرير الطلبات")
                 ->setDescription("تقرير الطلبات")
                 ->setKeywords("تقرير الطلبات")
                 ->setCategory("تقرير الطلبات");
    
    $objPHPExcel->setActiveSheetIndex(0)
              ->setRightToLeft(true);
	//filling the first row with 1-31 (month days)
	foreach ($number as $letter => $num)
	{
		$objPHPExcel->setActiveSheetIndex(0)
		     ->setCellValue($letter."1", $num);        
	}
        $j=0;
		foreach($aparts as $apart)
        {
          
          $objPHPExcel->setActiveSheetIndex(0)
              ->setCellValue('A'.($j+2), $apart['apart_name']);
			foreach ($number as $letter => $num)
            {
              if($marks[$j][$num] == 1)
              {
                $objPHPExcel->getActiveSheet()->getStyle($letter.($j+2))->getFill()
				->setFillType(PHPExcel_Style_Fill::FILL_SOLID)
				->getStartColor()->setARGB('135905');
              }
              else if($marks[$j][$num] == 2)
              {
                $objPHPExcel->getActiveSheet()->getStyle($letter.($j+2))->getFill()
				->setFillType(PHPExcel_Style_Fill::FILL_SOLID)
				->getStartColor()->setARGB('d03a3a');
			  }
            } 
			$j++;
		}
		
		 
        

      $filename = "unit_report";    
    
  }
  //////////////////////////////////////////////////////////////////////////////////////////
    if($_POST['filteration'] == 10){
	$filename = "requests_reports";
	$sql = "SELECT * FROM requests WHERE owner_id = ? ";
   $binds= array();
   $binds['user_id']= $_SESSION['user_id'];
    if(!empty($_POST['payment_name']))
    {
      $binds['payment_name'] = $_POST['payment_name'];
      $sql .=" and apart_name=? ";
	  $sqlCount .= " and apart_name=? "; 
    }
    
    $requsts = query($sql,$binds);
	
	$objPHPExcel->getProperties()->setCreator("Diafa Application")
                 ->setLastModifiedBy("Diafa Application")
                 ->setTitle("تقرير الطلبات")
                 ->setSubject("تقرير الطلبات")
                 ->setDescription("تقرير الطلبات")
                 ->setKeywords("تقرير الطلبات")
                 ->setCategory("تقرير الطلبات");
    
    $objPHPExcel->setActiveSheetIndex(0)
              ->setRightToLeft(true)
              ->setCellValue('A1', 'رقم الوحده')
              ->setCellValue('B1', 'تاريخ فتح الطلب')
              ->setCellValue('C1', 'وقت فتح الطلب')
              ->setCellValue('D1', 'تاريخ قفل الطلب')
              ->setCellValue('E1', 'وقت قفل الطلب')
              ->setCellValue('F1', 'مدة الانجاز') ;
			
    // Miscellaneous glyphs, UTF-8
	
    $i = 2;
    foreach($requsts as $requst) {
    
	if($requst['date_ending']=="0000-00-00")
	{
	$time="لم يتم انجازها" ;
	}
	else {
				$total_date= ($requst['date_ending']-$requst['date_beginning'])/86400 ;
			    $total_time =(strtotime($requst['time_ending']) - strtotime($requst['time_beginning']))/60 ;
				if($total_time <= 60){
			      $time = $total_date." يوم و".round($total_time)." دقيقة ";
			   }
			   else if($total_time >= 60 ){
			     $time =$total_date." يوم و ".round($total_time/60)." ساعة ";
			   }   
			   }
      $objPHPExcel->setActiveSheetIndex(0)
                ->setCellValue('A'.$i, $requst['apart_name'])
                ->setCellValue('B'.$i, $requst['date_beginning'])
                ->setCellValue('C'.$i, $requst['time_beginning'])
                ->setCellValue('D'.$i, $requst['date_ending'])
                ->setCellValue('E'.$i, $requst['time_ending'])
                ->setCellValue('F'.$i, $time );
               
      $i++;
    }
      
  }
    if($_POST['filteration'] == 11){
	
	$filename = "traffic_reports";
	
	  $sql = "SELECT * FROM apartments WHERE owner_id = ? ";
   
   $binds= array();
   $binds['user_id']= $_SESSION['user_id'];
    if(!empty($_POST['payment_name']))
    {
      $binds['payment_name'] = $_POST['payment_name'];
      $sql .=" and apart_name=? ";
	  
    }
	if(!empty($_POST['floor']))
    {
      $binds['floor'] = $_POST['floor'];
      $sql .=" and floor=? ";
	  
    }
	if(!empty($_POST['apart_type']))
    {
      $binds['apart_type'] = $_POST['apart_type'];
      $sql .=" and room_type=? ";
	 
    }
	 $traffic = query($sql,$binds);
	 
	if(isset($_POST['from']) && isset($_POST['to']))
    {
      $binds['from'] = $_POST['from'];
	  $binds['to'] = $_POST['to'];
	  
    }
   
	
	$objPHPExcel->getProperties()->setCreator("Diafa Application")
                 ->setLastModifiedBy("Diafa Application")
                 ->setTitle("تقرير نسبة الاشغال")
                 ->setSubject("تقرير نسبة الاشغال")
                 ->setDescription("تقرير نسبة الاشغال")
                 ->setKeywords("تقرير نسبة الاشغال")
                 ->setCategory("تقرير نسبة الاشغال");
    
    $objPHPExcel->setActiveSheetIndex(0)
              ->setRightToLeft(true)
              ->setCellValue('A1', 'رقم الوحده')
              ->setCellValue('B1', 'نسبة الاشغال') ;
              
			  $i=2;
			  if(!empty($traffic)){
				foreach($traffic as $traf) {
				 $total = (strtotime($binds['to'])-strtotime($binds['from']))/86400 ;
					$per = query("SELECT SUM(period) FROM contract_apartments LEFT JOIN contracts ON contract_apartments.contract_id=contracts.contract_id WHERE contract_apartments.apartment_id = ?",$traf['id']);
						  $percentage = ($per[0]['SUM(period)'] /$total)*100 ;
						$sum = $sum+$percentage; 
				 $objPHPExcel->setActiveSheetIndex(0)
					->setCellValue('A'.$i, $traf['apart_name'])
					->setCellValue('B'.$i, "%".round($percentage,2));
					
					$i++ ;
				}
			  
			  }
	}
  else if($_POST['filteration'] == 18) //unit Reports
  {
    
  if(isset($_POST['month']))
  {
    $month = $_POST['month'];
    $year = $_POST['year'];
  }
  else
  {
    $month = date('m', time());
    $year = date('Y', time());
  }
  
  
  $sql="SELECT * FROM apartments
		WHERE apartments.owner_id=?";
		
  $sqlCount= "SELECT COUNT(*) FROM apartments
		WHERE apartments.owner_id=?";
      
  $binds = array();
  $binds['owner_id'] = $_SESSION['user_id'];
	
  if(!empty($_POST['floor#']))
  {
    if(!empty($binds))
      $and = " and ";
    else
      $and = "  ";
          
    $binds['floor#'] = $_POST['floor#'];
    $sql .= $and." floor = ?";
    $sqlCount .= $and." floor = ?";
  }
  
  if(!empty($_POST['room_name']))
  {
    if(!empty($binds))
      $and = " and ";
    else
      $and = "  ";
          
    $binds['room_name'] = $_POST['room_name'];
    $sql .= $and." apart_name = ?";
    $sqlCount .= $and." apart_name = ?";
  }
  
  $aparts = query($sql,$binds);
  $prices = array();
  $j = 0;
  foreach($aparts as $apart)
  {
    
    $contracts = query("SELECT * FROM contract_apartments LEFT JOIN contracts ON contracts.contract_id = contract_apartments.contract_id LEFT JOIN companies ON contracts.company_id=companies.company_id WHERE apartment_id = ? 
    and (MONTH(mildai_from) =? and year(mildai_from) =? or (MONTH(created_at) = ? and year(created_at) =?)) ORDER BY created_at", $apart['id'],$month,$year,$month,$year);
    
    foreach($contracts as $contract)
    {
      $timestamp = strtotime($contract['mildai_from']);
      $day_in = intval(date('d', $timestamp));
      
      $timestamp = strtotime($contract['miladi_out']);
      $day_out = intval(date('d', $timestamp));
      if($day_out< $day_in) //$day_out extends to other month
      {
        $max_day = cal_days_in_month ( CAL_GREGORIAN, $month, $year ); //return max number of date for a given month
        $day_out =  $max_day;
      }
      for($k = $day_in; $k <$day_out;$k++)
      {
        $price_discounted = $contract['total'] - (  ( ($contract['discount'] + $contract['co_discount']) )  ) - $contract['fixed_discount'];
        $prices[$j][$k] = round($price_discounted/$contract['period'],2);
      }
    }
    $j++;
  }

	// table to hold excel cells vs numbers
	$numbers= array();
	$number['B'] = 1;
	$number['C'] = 2;
	$number['D'] = 3;
	$number['E'] = 4;
	$number['F'] = 5;
	$number['G'] = 6;
	$number['H'] = 7;
	$number['I'] = 8;
	$number['J'] = 9;
	$number['K'] = 10;
	$number['L'] = 11;
	$number['M'] = 12;
	$number['N'] = 13;
	$number['O'] = 14;
	$number['P'] = 15;
	$number['Q'] = 16;
	$number['R'] = 17;
	$number['S'] = 18;
	$number['T'] = 19;
	$number['U'] = 20;
	$number['V'] = 21;
	$number['W'] = 22;
	$number['X'] = 23;
	$number['Y'] = 24;
	$number['Z'] = 25;
	$number['AA'] = 26;
	$number['AB'] = 27;
	$number['AC'] = 28;
	$number['AD'] = 29;
	$number['AE'] = 30;
	$number['AF'] = 31;
    $objPHPExcel->getProperties()->setCreator("Diafa Application")
                 ->setLastModifiedBy("Diafa Application")
                 ->setTitle("تقرير الحركة الشهرية")
                 ->setSubject("تقرير الحركة الشهرية")
                 ->setDescription("تقرير الحركة الشهرية")
                 ->setKeywords("تقرير الحركة الشهرية")
                 ->setCategory("تقرير الحركة الشهرية");
    
    $objPHPExcel->setActiveSheetIndex(0)
              ->setRightToLeft(true);
	//filling the first row with 1-31 (month days)
	foreach ($number as $letter => $num)
	{
		$objPHPExcel->setActiveSheetIndex(0)
		     ->setCellValue($letter."1", $num);        
	}
	$objPHPExcel->setActiveSheetIndex(0)
		     ->setCellValue("AG1", "الإجمالى");   
        $j=0;
		foreach($aparts as $apart)
        {
          
          $objPHPExcel->setActiveSheetIndex(0)
              ->setCellValue('A'.($j+2), $apart['apart_name']);
			 $total = 0;
			foreach ($number as $letter => $num)
            {
				if(isset($prices[$j][$num-1]))
				{
				  $total += $prices[$j][$num-1];
					$objPHPExcel->getActiveSheet()->getStyle($letter.($j+2))->getFill()
					->setFillType(PHPExcel_Style_Fill::FILL_SOLID)
					->getStartColor()->setARGB('ecd40a');
					$objPHPExcel->setActiveSheetIndex(0)
					->setCellValue($letter.($j+2), $prices[$j][$num-1]);        
				}
				
            }
			$objPHPExcel->setActiveSheetIndex(0)
			->setCellValue("AG".($j+2), $total);   
			$j++;
		}
		
		 
        

      $filename = "monthly_report";    
    
  }
  if($_POST['filteration'] == 19){
    $sql_p = "SELECT * FROM payment_vouchers WHERE payment_type = 1 and owner_id= ?";
    $sql_r = "SELECT * FROM recipt_vouchers WHERE payment_type = 1 and owner_id= ?";
    
    $binds['owner_id'] = $_SESSION['user_id'];
  
    if(!empty($_POST['from']) && !empty($_POST['to']))
    {
      $binds['from'] = $_POST['from'];
      $binds['to'] = $_POST['to'];
      $sql_p .= " and miladi_date between ? AND ?";
      $sql_r .= " and miladi_date between ? AND ?";
    }
    $payments_counter = query($sql_p,$binds);
    $reciepts_counter = query($sql_r,$binds);
      
    
    $payments = query($sql_p,$binds);
	$reciepts = query($sql_r,$binds);
  
	$i = 0;
	$box = array();
	$boxes = array();

	while(1)
	{
    if(isset($payments[$i]) || isset($reciepts[$i]))
		{
			if(isset($payments[$i]))
				$payment = $payments[$i];
			else
      {
				$payment = array();
        $reciept['recipet_id'] = "";
				$reciept['amount']= "";
				$reciept['reason']= "";
				$reciept['miladi_date']= "";
      }
			
			if(isset($reciepts[$i]))
				$reciept = $reciepts[$i];
				
			else
			{
				$reciept = array();
				$reciept['recipet_id'] = "";
				$reciept['amount']= "";
				$reciept['reason']= "";
				$reciept['miladi_date']= "";
			}
			
			$box = $payment;
			$box['recipet_id'] = $reciept['recipet_id'];
			$box['reciept_amount'] = $reciept['amount'];
			$box['reciept_reason'] = $reciept['reason'];
			$box['reciept_miladi_date'] = $reciept['miladi_date'];
      $boxes[] = $box;
      $i++;
    }
		else
			break;
	}
      $objPHPExcel->getProperties()->setCreator("Diafa Application")
                 ->setLastModifiedBy("Diafa Application")
                 ->setTitle("تقرير حركة الصندوق")
                 ->setSubject("تقرير حركة الصندوق")
                 ->setDescription("تقرير حركة الصندوق")
                 ->setKeywords("تقرير حركة الصندوق")
                 ->setCategory("تقرير حركة الصندوق");
    
    $objPHPExcel->setActiveSheetIndex(0)
              ->setRightToLeft(true)
              ->setCellValue('A1', 'رقم سند الصرف')
              ->setCellValue('B1', 'بتاريخ')
              ->setCellValue('C1', 'من اجل')
              ->setCellValue('D1', 'مبلغ سند الصرف')
              ->setCellValue('E1', 'رقم سند القبض')
              ->setCellValue('F1', 'بتاريخ')
              ->setCellValue('G1', 'من اجل')
              ->setCellValue('H1', 'مبلغ سند القبض');
			  $filename = "box_reports";
			  $i=2;
              foreach($boxes as $box) {
			   $objPHPExcel->setActiveSheetIndex(0)
			  
			    ->setCellValue('A'.$i, $box['payment_id'])
                ->setCellValue('B'.$i, $box['miladi_date'])
                ->setCellValue('C'.$i, $box['reason'])
                ->setCellValue('D'.$i, $box['amount'])
                ->setCellValue('E'.$i, $box['recipet_id'])
                ->setCellValue('F'.$i, $box['reciept_miladi_date'])
                ->setCellValue('G'.$i, $box['reciept_reason'])
                ->setCellValue('H'.$i, $box['reciept_amount'] ) ;
                
      $i++;
			  
			  }
  
  }
	$objPHPExcel->getActiveSheet()->getPageSetup()->setPaperSize(PHPExcel_Worksheet_PageSetup::PAPERSIZE_A4);
	$objPHPExcel->getActiveSheet()->getPageSetup()->setOrientation(PHPExcel_Worksheet_PageSetup::ORIENTATION_LANDSCAPE);
$objPHPExcel->getActiveSheet()->getPageSetup()->setFitToPage(true);
$objPHPExcel->getActiveSheet()->getPageSetup()->setFitToWidth(1);
$objPHPExcel->getActiveSheet()->getPageSetup()->setFitToHeight(0);
	
  /////////////////////////////////////////////////////////////////////////////////////////
      //Create Excel Head row
    /*$objPHPExcel->getProperties()->setCreator("Diafa Application")
                 ->setLastModifiedBy("Diafa Application")
                 ->setTitle("تقرير حركة الشقق")
                 ->setSubject("تقرير حركة الشقق")
                 ->setDescription("تقرير حركة الشقق")
                 ->setKeywords("تقرير حركة الشقق")
                 ->setCategory("تقرير حركة الشقق");
    
    $objPHPExcel->setActiveSheetIndex(0)
              ->setRightToLeft(true)
              ->setCellValue('A1', 'رقم الوحدة');
    for($i = 2;$i<=32;$i++)
    {
      $objPHPExcel->setActiveSheetIndex(0)
                ->setCellValue(getNameFromNumber($i - 1).$i, 'رقم الوحدة');
    }
              
              ->setCellValue('B1', 'الوحدة')
              ->setCellValue('C1', 'رقم العقد')
              ->setCellValue('D1', 'تاريخ الدخول')
              ->setCellValue('E1', 'تاريخ الخروج')
              ->setCellValue('F1', 'المدة')
              ->setCellValue('G1', 'الإجمالى')
              ->setCellValue('H1', 'المدفوع')
              ->setCellValue('I1', 'المتبقى');

    $filename = "Unit_reports"; */   
  //here -- 
  
  if($_POST['pdf_excel'] == 1) //export PDF
  {
    if (!PHPExcel_Settings::setPdfRenderer(
        $rendererName,
        $rendererLibraryPath
      )) {
      die(
        'NOTICE: Please set the $rendererName and $rendererLibraryPath values' .
        '<br />' .
        'at the top of this script as appropriate for your directory structure'
      );
    }


    // Redirect output to a client’s web browser (PDF)
    header('Content-Type: application/pdf');
    header('Content-Disposition: attachment;filename='.$filename.'.pdf');
    header('Cache-Control: max-age=0');

    $objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'PDF');
    $objWriter->save('php://output');
    exit;
  }
  else //export Excel
  {
    // Set active sheet index to the first sheet, so Excel opens this as the first sheet
$objPHPExcel->setActiveSheetIndex(0);
    header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
    header('Content-Disposition: attachment;filename=' . $filename.".xlsx");
    header('Cache-Control: max-age=0');
    // If you're serving to IE 9, then the following may be needed
    header('Cache-Control: max-age=1');

    // If you're serving to IE over SSL, then the following may be needed
    header ('Expires: Mon, 26 Jul 1997 05:00:00 GMT'); // Date in the past
    header ('Last-Modified: '.gmdate('D, d M Y H:i:s').' GMT'); // always modified
    header ('Cache-Control: cache, must-revalidate'); // HTTP/1.1
    header ('Pragma: public'); // HTTP/1.0

    $objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel2007');
    ob_end_clean();
    $objWriter->save('php://output');
    exit;
    
  }
    
?>